package JDBC;

import com.mysql.cj.jdbc.Driver;
import com.sun.org.apache.bcel.internal.generic.DREM;
import org.junit.Test;

import java.nio.channels.ClosedSelectorException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Testt {

    @Test  //增加
    public void insert() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?useSSL=false", "root", "080666");

        String sql = "insert into table_name(id,name,age) values(?,?,?)";

        PreparedStatement preparedStatement = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setInt(1, 9);
        preparedStatement.setString(2, "L");
        preparedStatement.setInt(3, 18);
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("插入成功");
            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            generatedKeys.next();
            int id = generatedKeys.getInt(1);
            System.out.println("id="+id);
        } else {
            System.out.println("插入失败");
        }
        preparedStatement.close();
        connection.close();

    }

    @Test //删除
    public void delete() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection root = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?useSSL=false", "root", "080666");

        String sql = "delete from table_name where id=?";
        PreparedStatement preparedStatement = root.prepareStatement(sql);
        preparedStatement.setInt(1, 7);
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
        preparedStatement.close();
        root.close();

    }

    @Test //修改
    public void update() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection root = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?useSSL=false", "root", "080666");
        String sql = "update table_name set name=? where id=?";
        PreparedStatement preparedStatement = root.prepareStatement(sql);
        preparedStatement.setObject(1, "yoona");
        preparedStatement.setObject(2, 1);
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("修改成功");
        } else {
            System.out.println("修改失败");
        }
        preparedStatement.close();
        root.close();

    }

    @Test  //查询
    public void select() throws SQLException {
        DriverManager.registerDriver(new Driver());//注册mysql的驱动
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?useSSL=false", "root", "080666");
        Statement statement = connection.createStatement();
        String sql = "select * from table_name";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            System.out.println(id + " " + name + " " + age);
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

    @Test
    public void select2() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection root = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?useSSL=false", "root", "080666");
        String sql = "select * from table_name";
        PreparedStatement preparedStatement = root.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Map> list = new ArrayList<Map>();
        while (resultSet.next()) {
            Map map=new HashMap();
            map.put("id", resultSet.getInt("id"));
            map.put("name", resultSet.getString("name"));
            map.put("age", resultSet.getInt("age"));
            list.add(map);
        }
        System.out.println("list="+list);
        resultSet.close();
        preparedStatement.close();
        root.close();

    }

    @Test
    public void select3() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection root = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?useSSL=false", "root", "080666");
        String sql = "select * from table_name";
        PreparedStatement preparedStatement = root.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Map> list = new ArrayList<Map>();

        ResultSetMetaData metaData = resultSet.getMetaData();//装的是当前列的结果集
        int columnCount = metaData.getColumnCount();//获取列数
        while (resultSet.next()) {
            Map map=new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                Object value = resultSet.getObject(i);
                String key = metaData.getColumnLabel(i);
                map.put(key,value);

            }
            list.add(map);

        }

        System.out.println(list);

        resultSet.close();
        preparedStatement.close();
        root.close();

    }


}